const mysql = require('mysql2/promise')
const config = require('./config')

async function fixDatabaseSafe () {
  try {
    const connection = await mysql.createConnection({
      host: config.database.host,
      port: config.database.port,
      user: config.database.user,
      password: config.database.password,
      database: config.database.name
    })

    console.log('🔧 开始安全修复数据库结构...')

    // 1. 先删除所有外键约束
    console.log('\n🔗 删除现有外键约束...')
    try {
      // 查询所有外键约束
      const [foreignKeys] = await connection.execute(`
        SELECT CONSTRAINT_NAME, TABLE_NAME 
        FROM information_schema.KEY_COLUMN_USAGE 
        WHERE REFERENCED_TABLE_SCHEMA = '${config.database.name}' 
        AND REFERENCED_TABLE_NAME = 'authors'
      `)

      for (const fk of foreignKeys) {
        console.log(`删除外键约束: ${fk.CONSTRAINT_NAME} 从表 ${fk.TABLE_NAME}`)
        await connection.execute(
          `ALTER TABLE ${fk.TABLE_NAME} DROP FOREIGN KEY ${fk.CONSTRAINT_NAME}`
        )
      }
      console.log('✅ 外键约束删除完成')
    } catch (error) {
      console.log('⚠️ 删除外键约束时出错:', error.message)
    }

    // 2. 删除并重新创建authors表
    console.log('\n📝 重新创建authors表...')
    await connection.execute('DROP TABLE IF EXISTS authors')
    await connection.execute(`
      CREATE TABLE authors (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100) NOT NULL UNIQUE,
        description TEXT,
        avatar_url VARCHAR(500) DEFAULT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX idx_name (name)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `)
    console.log('✅ authors表创建成功')

    // 3. 插入作者数据
    console.log('\n👥 插入作者数据...')
    const authors = [
      {
        id: 1,
        name: '天蚕土豆',
        description: '网络文学作家，代表作品有《斗破苍穹》、《武动乾坤》等。'
      },
      {
        id: 2,
        name: '辰东',
        description:
          '网络文学作家，代表作品有《完美世界》、《遮天》、《圣墟》等。'
      },
      {
        id: 3,
        name: '叶天南',
        description: '网络文学作家，擅长都市修真类小说。'
      },
      {
        id: 4,
        name: '我吃西红柿',
        description:
          '网络文学作家，代表作品有《盘龙》、《星辰变》、《莽荒纪》等。'
      },
      {
        id: 5,
        name: '猫腻',
        description:
          '网络文学作家，代表作品有《庆余年》、《将夜》、《择天记》等。'
      },
      {
        id: 6,
        name: '烽火戏诸侯',
        description: '网络文学作家，代表作品有《雪中悍刀行》、《剑来》等。'
      },
      {
        id: 7,
        name: '唐家三少',
        description: '中国网络文学作家，作品包括《斗罗大陆》、《绝世唐门》等。'
      }
    ]

    for (const author of authors) {
      await connection.execute(
        'INSERT INTO authors (id, name, description) VALUES (?, ?, ?)',
        [author.id, author.name, author.description]
      )
    }
    console.log('✅ 作者数据插入完成')

    // 4. 检查并修改novels表结构
    console.log('\n📚 调整novels表结构...')

    const [currentStructure] = await connection.execute('DESCRIBE novels')
    console.log('当前novels表结构:')
    console.table(currentStructure)

    // 按照模拟数据结构添加需要的字段
    const fieldsToAdd = [
      { name: 'author_id', type: 'INT DEFAULT NULL' },
      { name: 'cover_url', type: 'VARCHAR(500) DEFAULT NULL' },
      { name: 'rating_count', type: 'INT DEFAULT 0' },
      { name: 'favorite_count', type: 'INT DEFAULT 0' },
      { name: 'is_vip', type: 'BOOLEAN DEFAULT FALSE' },
      { name: 'price', type: 'INT DEFAULT 0' },
      { name: 'last_chapter_title', type: 'VARCHAR(255) DEFAULT NULL' },
      { name: 'last_chapter_time', type: 'DATETIME DEFAULT NULL' }
    ]

    for (const field of fieldsToAdd) {
      const hasField = currentStructure.some(col => col.Field === field.name)
      if (!hasField) {
        console.log(`添加字段: ${field.name}`)
        await connection.execute(
          `ALTER TABLE novels ADD COLUMN ${field.name} ${field.type}`
        )
      } else {
        console.log(`字段 ${field.name} 已存在`)
      }
    }

    // 删除不需要的字段
    const fieldsToRemove = ['like_count']
    for (const fieldName of fieldsToRemove) {
      const hasField = currentStructure.some(col => col.Field === fieldName)
      if (hasField) {
        console.log(`删除字段: ${fieldName}`)
        await connection.execute(`ALTER TABLE novels DROP COLUMN ${fieldName}`)
      }
    }

    // 5. 添加索引
    console.log('\n📊 添加索引...')
    const indexes = [
      { name: 'idx_author_id', column: 'author_id' },
      { name: 'idx_favorite_count', column: 'favorite_count' },
      { name: 'idx_rating', column: 'rating' },
      { name: 'idx_view_count', column: 'view_count' }
    ]

    for (const index of indexes) {
      try {
        await connection.execute(
          `ALTER TABLE novels ADD INDEX ${index.name} (${index.column})`
        )
        console.log(`✅ 添加索引: ${index.name}`)
      } catch (error) {
        if (error.code === 'ER_DUP_KEYNAME') {
          console.log(`索引 ${index.name} 已存在`)
        } else {
          console.log(`⚠️ 添加索引 ${index.name} 失败:`, error.message)
        }
      }
    }

    // 6. 重新添加外键约束
    console.log('\n🔗 添加外键约束...')
    try {
      await connection.execute(
        'ALTER TABLE novels ADD FOREIGN KEY (author_id) REFERENCES authors(id)'
      )
      console.log('✅ 外键约束添加成功')
    } catch (error) {
      console.log('⚠️ 外键约束添加失败:', error.message)
    }

    // 7. 更新现有小说数据的author_id
    console.log('\n🔄 更新现有小说的作者关联...')

    const authorMap = {
      天蚕土豆: 1,
      辰东: 2,
      叶天南: 3,
      我吃西红柿: 4,
      猫腻: 5,
      烽火戏诸侯: 6,
      唐家三少: 7
    }

    const [novels] = await connection.execute(
      'SELECT id, title, author FROM novels'
    )
    for (const novel of novels) {
      const authorId = authorMap[novel.author]
      if (authorId) {
        await connection.execute(
          'UPDATE novels SET author_id = ? WHERE id = ?',
          [authorId, novel.id]
        )
        console.log(`✅ 更新《${novel.title}》的作者ID: ${authorId}`)
      } else {
        console.log(
          `⚠️ 未找到作者《${novel.author}》的ID，跳过《${novel.title}》`
        )
      }
    }

    // 8. 显示最终结果
    console.log('\n📊 最终的authors表数据:')
    const [finalAuthors] = await connection.execute(
      'SELECT * FROM authors ORDER BY id'
    )
    console.table(finalAuthors)

    console.log('\n📚 最终的novels表结构:')
    const [finalNovelStructure] = await connection.execute('DESCRIBE novels')
    console.table(finalNovelStructure)

    console.log('\n🔗 novels表与authors的关联:')
    const [novelAuthorJoin] = await connection.execute(`
      SELECT n.id, n.title, n.author, a.name as author_name, n.author_id 
      FROM novels n 
      LEFT JOIN authors a ON n.author_id = a.id 
      LIMIT 10
    `)
    console.table(novelAuthorJoin)

    await connection.end()
    console.log('\n🎉 数据库结构修复完成！')
    console.log('\n📋 现在数据库结构与模拟数据完全匹配：')
    console.log('- ✅ authors表已重新创建并填充数据')
    console.log('- ✅ novels表已添加所有必要字段')
    console.log('- ✅ 外键关系已建立')
    console.log('- ✅ 索引已优化')
  } catch (error) {
    console.error('❌ 数据库修复失败:', error.message)
    console.error(error)
  }
}

fixDatabaseSafe()
